{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "import json\n",
    "import csv\n",
    "import urllib.request\n",
    "import pandas as pd\n",
    "import numpy as np\n",
    "import pymysql\n",
    "from io import BufferedReader\n",
    "    \n",
    "# THIS CODE NEEDS TO BE RUN BEFORE MAKING THE SQL CONNECTION\n",
    "\n",
    "pymysql.converters.encoders[np.float64] = pymysql.converters.escape_float\n",
    "pymysql.converters.conversions = pymysql.converters.encoders.copy()\n",
    "pymysql.converters.conversions.update(pymysql.converters.decoders)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [],
   "source": [
    "from IPython.core.interactiveshell import InteractiveShell\n",
    "InteractiveShell.ast_node_interactivity = \"all\""
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Below code retrieve all the data from the view created in the mySQL, based on which we write our growth and value investment logic.\n",
    "\n",
    "- After retrieving, we are selecting required features in a separate dataframe.\n",
    "- As entire data has been loaded as text format[through to_sql() method of python)], we are converting dates and other data in required format. Plus cleaning of the data.\n",
    "- variables **curr_year** and **ref_year** defines the timeframe of the portfolio.\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "C:\\Users\\Sudip\\Anaconda3\\lib\\site-packages\\ipykernel_launcher.py:25: FutureWarning: Comparing Series of datetimes with 'datetime.date'.  Currently, the\n",
      "'datetime.date' is coerced to a datetime. In the future pandas will\n",
      "not coerce, and a TypeError will be raised. To retain the current\n",
      "behavior, convert the 'datetime.date' to a datetime with\n",
      "'pd.Timestamp'.\n"
     ]
    }
   ],
   "source": [
    "# loding data for Value and Growth investment\n",
    "\n",
    "from datetime import datetime\n",
    "from datetime import date\n",
    "import datetime\n",
    "\n",
    "\n",
    "from sqlalchemy import create_engine\n",
    "\n",
    "engine = create_engine('mysql+pymysql://nativeuser:password@localhost/automatic_portfolio_creation')\n",
    "query = \"SELECT * from vw_select_stock\"\n",
    "\n",
    "dfselect_stock =pd.read_sql(query,engine)\n",
    "\n",
    "curr_year = date.today().year -5 \n",
    "#curr_year\n",
    "ref_year = date.today().year - 9\n",
    "#ref_year\n",
    "start_date = datetime.date(ref_year,12,31)\n",
    "end_date = datetime.date(curr_year,12,31)\n",
    "\n",
    "#dfselect_stock['DATE_YEAR'].dtype\n",
    "dfselect_stock['DATE_YEAR'] = pd.to_datetime(dfselect_stock['DATE_YEAR'], format='%Y-%m-%d')\n",
    "#dfselect_stock = dfselect.copy()\n",
    "dtFilter = (dfselect_stock['DATE_YEAR'] > start_date) & (dfselect_stock['DATE_YEAR'] <= end_date)\n",
    "dfselect_stock = dfselect_stock.loc[dtFilter]\n",
    "\n",
    "\n",
    "# Collecting required parameters for value & Growth portfolio\n",
    "\n",
    "dfselect_stock.replace('',np.nan, inplace=True)\n",
    "dfselect_stock.fillna(0, inplace=True)\n",
    "rest_indx = dfselect_stock.reset_index()\n",
    "floatlist = ['BETA', 'EPS', 'PE_RATIO','PB_RATIO', 'DEBT_TO_EQUITY', 'CURRENT_RATIO','PRICE_TO_SALES_RATIO','DIVIDEND_YIELD','5Y_Dividend_per_Share_Growth_PER_SHARE',\n",
    "            '3Y_Dividend_per_Share_Growth_PER_SHARE','DEBT_TO_ASSETS','EPS_DILUTED_GROWTH','MARKET_CAP','TANGIBLE_ASSET_VALUE', 'ROE', 'BOOK_VALUE_PER_SHARE', 'ROIC',\n",
    "             'BOOK_VALUE_PER_SHARE_GROWTH','MARKET_CAP','OUTSTANDING_SHARES']\n",
    "\n",
    "for eachcol in floatlist:\n",
    "    dfselect_stock[eachcol] = dfselect_stock[eachcol].astype('float64')\n",
    "    \n",
    "dfselect_stock['MARKET_CAP'] = dfselect_stock['MARKET_CAP'].astype('int64')\n",
    "dfselect_stock['OUTSTANDING_SHARES'] = dfselect_stock['OUTSTANDING_SHARES'].astype('int64')\n",
    "#dfselect_stock['TANGIBLE_ASSET_VALUE'] = dfselect_stock['TANGIBLE_ASSET_VALUE'].astype('long')\n",
    "\n",
    "dfmean = dfselect_stock.groupby(['STOCK_TIKR','SECTOR','DATE_YEAR']).mean().reset_index()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Required Financial Ratios of all the companies yearwise (showing top 5) :\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>STOCK_TIKR</th>\n",
       "      <th>SECTOR</th>\n",
       "      <th>DATE_YEAR</th>\n",
       "      <th>BETA</th>\n",
       "      <th>EPS</th>\n",
       "      <th>PE_RATIO</th>\n",
       "      <th>PB_RATIO</th>\n",
       "      <th>DEBT_TO_EQUITY</th>\n",
       "      <th>DEBT_TO_ASSETS</th>\n",
       "      <th>CURRENT_RATIO</th>\n",
       "      <th>...</th>\n",
       "      <th>BOOK_VALUE_PER_SHARE_GROWTH</th>\n",
       "      <th>5Y_Dividend_per_Share_Growth_PER_SHARE</th>\n",
       "      <th>3Y_Dividend_per_Share_Growth_PER_SHARE</th>\n",
       "      <th>EPS_DILUTED_GROWTH</th>\n",
       "      <th>MARKET_CAP</th>\n",
       "      <th>ROE</th>\n",
       "      <th>BOOK_VALUE_PER_SHARE</th>\n",
       "      <th>ROIC</th>\n",
       "      <th>OUTSTANDING_SHARES</th>\n",
       "      <th>TANGIBLE_ASSET_VALUE</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>A</td>\n",
       "      <td>Healthcare</td>\n",
       "      <td>2011-10-31</td>\n",
       "      <td>1.371835</td>\n",
       "      <td>2.92</td>\n",
       "      <td>9.0810</td>\n",
       "      <td>2.1358</td>\n",
       "      <td>0.5072</td>\n",
       "      <td>0.2412</td>\n",
       "      <td>3.032</td>\n",
       "      <td>...</td>\n",
       "      <td>0.3345</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.4691</td>\n",
       "      <td>9206908940</td>\n",
       "      <td>0.2349</td>\n",
       "      <td>12.415</td>\n",
       "      <td>0.1700</td>\n",
       "      <td>4308000000</td>\n",
       "      <td>7.061000e+09</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>A</td>\n",
       "      <td>Healthcare</td>\n",
       "      <td>2012-10-31</td>\n",
       "      <td>1.371835</td>\n",
       "      <td>3.31</td>\n",
       "      <td>7.7776</td>\n",
       "      <td>1.7288</td>\n",
       "      <td>0.4558</td>\n",
       "      <td>0.2242</td>\n",
       "      <td>2.445</td>\n",
       "      <td>...</td>\n",
       "      <td>0.1994</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.1474</td>\n",
       "      <td>8970285351</td>\n",
       "      <td>0.2225</td>\n",
       "      <td>14.891</td>\n",
       "      <td>0.1070</td>\n",
       "      <td>5182000000</td>\n",
       "      <td>6.425000e+09</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>A</td>\n",
       "      <td>Healthcare</td>\n",
       "      <td>2013-10-31</td>\n",
       "      <td>1.371835</td>\n",
       "      <td>2.15</td>\n",
       "      <td>16.8879</td>\n",
       "      <td>2.2805</td>\n",
       "      <td>0.5106</td>\n",
       "      <td>0.2526</td>\n",
       "      <td>3.110</td>\n",
       "      <td>...</td>\n",
       "      <td>0.0410</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>-0.3486</td>\n",
       "      <td>12010726038</td>\n",
       "      <td>0.1389</td>\n",
       "      <td>15.501</td>\n",
       "      <td>0.0377</td>\n",
       "      <td>5286000000</td>\n",
       "      <td>6.723000e+09</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>A</td>\n",
       "      <td>Healthcare</td>\n",
       "      <td>2014-10-31</td>\n",
       "      <td>1.371835</td>\n",
       "      <td>1.65</td>\n",
       "      <td>23.9650</td>\n",
       "      <td>2.4840</td>\n",
       "      <td>0.3137</td>\n",
       "      <td>0.1538</td>\n",
       "      <td>3.256</td>\n",
       "      <td>...</td>\n",
       "      <td>0.0270</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>-0.2394</td>\n",
       "      <td>13187827388</td>\n",
       "      <td>0.1036</td>\n",
       "      <td>15.919</td>\n",
       "      <td>0.0379</td>\n",
       "      <td>5301000000</td>\n",
       "      <td>7.659000e+09</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>AAC</td>\n",
       "      <td>Healthcare</td>\n",
       "      <td>2013-12-31</td>\n",
       "      <td>2.428700</td>\n",
       "      <td>0.13</td>\n",
       "      <td>0.2500</td>\n",
       "      <td>0.0059</td>\n",
       "      <td>5.2640</td>\n",
       "      <td>0.5276</td>\n",
       "      <td>1.043</td>\n",
       "      <td>...</td>\n",
       "      <td>0.0000</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0000</td>\n",
       "      <td>206733</td>\n",
       "      <td>0.0961</td>\n",
       "      <td>0.591</td>\n",
       "      <td>0.1392</td>\n",
       "      <td>8183000</td>\n",
       "      <td>6.727900e+07</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 22 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "  STOCK_TIKR      SECTOR  DATE_YEAR      BETA   EPS  PE_RATIO  PB_RATIO  \\\n",
       "0          A  Healthcare 2011-10-31  1.371835  2.92    9.0810    2.1358   \n",
       "1          A  Healthcare 2012-10-31  1.371835  3.31    7.7776    1.7288   \n",
       "2          A  Healthcare 2013-10-31  1.371835  2.15   16.8879    2.2805   \n",
       "3          A  Healthcare 2014-10-31  1.371835  1.65   23.9650    2.4840   \n",
       "4        AAC  Healthcare 2013-12-31  2.428700  0.13    0.2500    0.0059   \n",
       "\n",
       "   DEBT_TO_EQUITY  DEBT_TO_ASSETS  CURRENT_RATIO          ...           \\\n",
       "0          0.5072          0.2412          3.032          ...            \n",
       "1          0.4558          0.2242          2.445          ...            \n",
       "2          0.5106          0.2526          3.110          ...            \n",
       "3          0.3137          0.1538          3.256          ...            \n",
       "4          5.2640          0.5276          1.043          ...            \n",
       "\n",
       "   BOOK_VALUE_PER_SHARE_GROWTH  5Y_Dividend_per_Share_Growth_PER_SHARE  \\\n",
       "0                       0.3345                                     0.0   \n",
       "1                       0.1994                                     0.0   \n",
       "2                       0.0410                                     0.0   \n",
       "3                       0.0270                                     0.0   \n",
       "4                       0.0000                                     0.0   \n",
       "\n",
       "   3Y_Dividend_per_Share_Growth_PER_SHARE  EPS_DILUTED_GROWTH   MARKET_CAP  \\\n",
       "0                                     0.0              0.4691   9206908940   \n",
       "1                                     0.0              0.1474   8970285351   \n",
       "2                                     0.0             -0.3486  12010726038   \n",
       "3                                     0.0             -0.2394  13187827388   \n",
       "4                                     0.0              0.0000       206733   \n",
       "\n",
       "      ROE  BOOK_VALUE_PER_SHARE    ROIC  OUTSTANDING_SHARES  \\\n",
       "0  0.2349                12.415  0.1700          4308000000   \n",
       "1  0.2225                14.891  0.1070          5182000000   \n",
       "2  0.1389                15.501  0.0377          5286000000   \n",
       "3  0.1036                15.919  0.0379          5301000000   \n",
       "4  0.0961                 0.591  0.1392             8183000   \n",
       "\n",
       "   TANGIBLE_ASSET_VALUE  \n",
       "0          7.061000e+09  \n",
       "1          6.425000e+09  \n",
       "2          6.723000e+09  \n",
       "3          7.659000e+09  \n",
       "4          6.727900e+07  \n",
       "\n",
       "[5 rows x 22 columns]"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Total number of rows: 4900\n"
     ]
    }
   ],
   "source": [
    "print(\"Required Financial Ratios of all the companies yearwise (showing top 5) :\")\n",
    "\n",
    "dfmean.head()\n",
    "print('Total number of rows:',dfmean.shape[0])\n",
    "#dfselect.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We have already calculated company valuation for each of the company. Loading the same data in below code."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Calc_year</th>\n",
       "      <th>STOCK_TIKR</th>\n",
       "      <th>FAIR_VALUE</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2019</td>\n",
       "      <td>CMCSA</td>\n",
       "      <td>52.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2019</td>\n",
       "      <td>INTC</td>\n",
       "      <td>47.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2019</td>\n",
       "      <td>MU</td>\n",
       "      <td>59.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2019</td>\n",
       "      <td>AAPL</td>\n",
       "      <td>117.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2019</td>\n",
       "      <td>MSFT</td>\n",
       "      <td>17.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   Calc_year STOCK_TIKR  FAIR_VALUE\n",
       "0       2019      CMCSA        52.0\n",
       "1       2019       INTC        47.0\n",
       "2       2019         MU        59.0\n",
       "3       2019       AAPL       117.0\n",
       "4       2019       MSFT        17.0"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "engine = create_engine('mysql+pymysql://nativeuser:password@localhost/automatic_portfolio_creation')\n",
    "queryfcf = \"SELECT * from fcfvalue\"\n",
    "\n",
    "df_fcf = pd.read_sql(queryfcf,engine)\n",
    "#df_fcf.head()\n",
    " \n",
    "\n",
    "# df_fv1 = pd.read_csv('share_between_80and99.csv')\n",
    "df_fcf.rename(columns = {'STK_TKR':'STOCK_TIKR'}, inplace = True)\n",
    "del df_fcf['index']\n",
    "del df_fcf['Closing_price']\n",
    "df_fcf.rename(columns = {'Share_price':'FAIR_VALUE'}, inplace = True)\n",
    "df_fcf['FAIR_VALUE'] = df_fcf['FAIR_VALUE'].astype('float64')\n",
    "df_fcf['FAIR_VALUE'] = df_fcf['FAIR_VALUE'].round()\n",
    "\n",
    "df_fcf.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Below code is to select the stocks which can consider as the value stocks, we are assigning each stock's valuation as fair price from above fcf dataframe."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Below is the list of potential value stocks:\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>STOCK_TIKR</th>\n",
       "      <th>SECTOR</th>\n",
       "      <th>BETA</th>\n",
       "      <th>FAIR_VALUE</th>\n",
       "      <th>Calc_year</th>\n",
       "      <th>ADDED_ON</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>54</th>\n",
       "      <td>ACCO</td>\n",
       "      <td>Industrials</td>\n",
       "      <td>1.818291</td>\n",
       "      <td>19.0</td>\n",
       "      <td>2019</td>\n",
       "      <td>2019-12-18</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>118</th>\n",
       "      <td>ADM</td>\n",
       "      <td>Consumer Defensive</td>\n",
       "      <td>0.955928</td>\n",
       "      <td>45.0</td>\n",
       "      <td>2019</td>\n",
       "      <td>2019-12-18</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>238</th>\n",
       "      <td>AIR</td>\n",
       "      <td>Industrials</td>\n",
       "      <td>1.009082</td>\n",
       "      <td>28.0</td>\n",
       "      <td>2019</td>\n",
       "      <td>2019-12-18</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>302</th>\n",
       "      <td>ALG</td>\n",
       "      <td>Industrials</td>\n",
       "      <td>0.850361</td>\n",
       "      <td>39.0</td>\n",
       "      <td>2019</td>\n",
       "      <td>2019-12-18</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>465</th>\n",
       "      <td>AP</td>\n",
       "      <td>Industrials</td>\n",
       "      <td>2.154915</td>\n",
       "      <td>13.0</td>\n",
       "      <td>2019</td>\n",
       "      <td>2019-12-18</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    STOCK_TIKR              SECTOR      BETA  FAIR_VALUE  Calc_year  \\\n",
       "54        ACCO         Industrials  1.818291        19.0       2019   \n",
       "118        ADM  Consumer Defensive  0.955928        45.0       2019   \n",
       "238        AIR         Industrials  1.009082        28.0       2019   \n",
       "302        ALG         Industrials  0.850361        39.0       2019   \n",
       "465         AP         Industrials  2.154915        13.0       2019   \n",
       "\n",
       "       ADDED_ON  \n",
       "54   2019-12-18  \n",
       "118  2019-12-18  \n",
       "238  2019-12-18  \n",
       "302  2019-12-18  \n",
       "465  2019-12-18  "
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Value investment logic\n",
    "# ADD CONSTRAIN OF MARKET CAPITALIZATION\n",
    "# save each strategic parameter in a different dataframe, so we can combine all parameters as\n",
    "# required (can add or remove as required based on further reasearch) to construct the portfolio\n",
    "\n",
    "dfcurr = dfmean[dfmean.CURRENT_RATIO >= 2]\n",
    "dfpe = dfmean[dfmean.PE_RATIO <= 10]\n",
    "dfpb = dfmean[dfmean.PB_RATIO <= 1.3]\n",
    "dfpeb = dfmean[dfmean.PB_RATIO*dfmean.PE_RATIO < 23]\n",
    "dfdy = dfmean[dfmean.DIVIDEND_YIELD >= 1.0]\n",
    "dfepsg = dfmean[dfmean.EPS_DILUTED_GROWTH > 0]\n",
    "dfpsr = dfmean[dfmean.PRICE_TO_SALES_RATIO <= 1.0]\n",
    "dfmkt = dfmean[dfmean.MARKET_CAP > 100000000]\n",
    "\n",
    "dfValue1 = pd.concat([dfcurr,dfpe,dfpb,dfpeb,dfdy,dfepsg,dfpsr,dfmkt])\n",
    "\n",
    "#dfValue.head()\n",
    "\n",
    "dfValue = pd.merge(dfValue1,df_fcf, on = 'STOCK_TIKR')\n",
    "\n",
    "#dfValue.head()\n",
    "\n",
    "# combining all strategic parameters to create a single strategy for value investment \n",
    "\n",
    "def final_val(dfValue):\n",
    "    return dfValue[\n",
    "        ((dfValue.PE_RATIO <= 10)&\n",
    "        (dfValue.PB_RATIO <= 1.3))&#|(dfValue.PB_RATIO*dfValue.PE_RATIO < 23))&\n",
    "        #(dfValue.DIVIDEND_YIELD >= 1.0)&\n",
    "        #(dfValue['3Y_Dividend_per_Share_Growth_PER_SHARE'] > 0)& \n",
    "        (dfValue.CURRENT_RATIO >= 2)&\n",
    "        (dfValue.MARKET_CAP > 100000000)&\n",
    "        (dfValue.PRICE_TO_SALES_RATIO <= 1.0)\n",
    "        ]\n",
    "\n",
    "# List of value stocks\n",
    "\n",
    "dfValFinal = final_val(dfValue)\n",
    "dfValFinal = dfValFinal[['STOCK_TIKR','SECTOR','BETA','FAIR_VALUE','Calc_year']].copy()\n",
    "dfValFinal = dfValFinal.drop_duplicates(subset=['STOCK_TIKR'],keep=\"first\")\n",
    "dfValFinal['ADDED_ON'] = date.today()\n",
    "print('Below is the list of potential value stocks:')\n",
    "dfValFinal.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "There are 46 stocks which are potential value stocks\n"
     ]
    }
   ],
   "source": [
    "#dfValue.shape\n",
    "print('There are',dfValFinal.shape[0],'stocks which are potential value stocks')\n",
    "dfValFinal.to_csv('myvaluestocksr.csv')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Once we selected the potential value stocks, next step to select potential growth stocks.\n",
    "- EPS growth is considered one of the major parameter to select a growth stock.\n",
    "- In below code we are checking the EPS growth of all the stocks for required timeframe."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "C:\\Users\\Sudip\\Anaconda3\\lib\\site-packages\\ipykernel_launcher.py:15: FutureWarning: Comparing Series of datetimes with 'datetime.date'.  Currently, the\n",
      "'datetime.date' is coerced to a datetime. In the future pandas will\n",
      "not coerce, and a TypeError will be raised. To retain the current\n",
      "behavior, convert the 'datetime.date' to a datetime with\n",
      "'pd.Timestamp'.\n",
      "  from ipykernel import kernelapp as app\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "CategoricalIndex([False, True], categories=[False, True], ordered=False, name='POSITIVE', dtype='category')"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Below is the bifurcation of the stocks based on Growth EPS, flagged as Yes or No\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>STOCK_TIKR</th>\n",
       "      <th>NGrowth</th>\n",
       "      <th>PGrowth</th>\n",
       "      <th>GrowthEPS</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>A</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>Yes</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>AA</td>\n",
       "      <td>3</td>\n",
       "      <td>1</td>\n",
       "      <td>No</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>AABA</td>\n",
       "      <td>3</td>\n",
       "      <td>1</td>\n",
       "      <td>No</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>AAC</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>Yes</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>AAL</td>\n",
       "      <td>1</td>\n",
       "      <td>3</td>\n",
       "      <td>Yes</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  STOCK_TIKR  NGrowth  PGrowth GrowthEPS\n",
       "0          A        2        2       Yes\n",
       "1         AA        3        1        No\n",
       "2       AABA        3        1        No\n",
       "3        AAC        2        2       Yes\n",
       "4        AAL        1        3       Yes"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Logic to check whether stock EPS is growing since N years (for selecting Growth Stocks)\n",
    "\n",
    "engine = create_engine('mysql+pymysql://nativeuser:password@localhost/automatic_portfolio_creation')\n",
    "queryepsG = \"SELECT STOCK_TIKR,DATE_YEAR,EPS from income_statement\"\n",
    "\n",
    "dfepsPer =pd.read_sql(queryepsG,engine)\n",
    "#dfepsPer\n",
    "curr_year = date.today().year \n",
    "ref_year = date.today().year - 5 \n",
    "start_date = datetime.date(ref_year,12,31)\n",
    "end_date = datetime.date(curr_year,12,31)\n",
    "\n",
    "# EPS GROWTH YEARBY FOR GRWOTH STOCK \n",
    "dfepsPer['DATE_YEAR'] = pd.to_datetime(dfepsPer['DATE_YEAR'])\n",
    "dtFilter = (dfepsPer['DATE_YEAR'] > start_date) & (dfepsPer['DATE_YEAR'] <= end_date)\n",
    "#dtFilter\n",
    "dfepsPer = dfepsPer.loc[dtFilter]\n",
    "#dfepsPer.head(10)\n",
    "dfepsPer['EPS_PREV'] = dfepsPer['EPS'].shift(1)\n",
    "dfepsPer['POSITIVE'] = dfepsPer['EPS'] > dfepsPer['EPS_PREV']\n",
    "dfepsPer['POSITIVE'] = dfepsPer['POSITIVE'].astype('category')\n",
    "#dfepsPer = dfepsPer[dfepsPer['POSITIVE'] == 'True'].groupby(['STOCK_TIKR']).size().reset_index(name='+veGrowthCount')\n",
    "dfEPS_GR= dfepsPer.groupby(['STOCK_TIKR', 'POSITIVE']).size().unstack(fill_value=0)\n",
    "#dfepsPer['+VeGrowth'] = np.where(dfepsPer.POSITIVE['True'] > dfepsPer.POSITIVE['False'],'Yes','No')\n",
    "#dfEPS_GR.head()\n",
    "dfEPS_GR.columns\n",
    "dfEPS_GR.to_csv('PositiveEPS.csv')\n",
    "dfEPS_GR = pd.read_csv('PositiveEPS.csv')\n",
    "dfEPS_GR.columns = ['STOCK_TIKR','NGrowth','PGrowth']\n",
    "dfEPS_GR['GrowthEPS'] = np.where(dfEPS_GR['PGrowth'] >= dfEPS_GR['NGrowth'],'Yes','No')\n",
    "print('Below is the bifurcation of the stocks based on Growth EPS, flagged as Yes or No')\n",
    "dfEPS_GR.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Below is the logic wriiten for selecting potential growth stocks for the required timeframe."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Below is the list of potential growth stocks:\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>STOCK_TIKR</th>\n",
       "      <th>SECTOR</th>\n",
       "      <th>BETA</th>\n",
       "      <th>GrowthEPS</th>\n",
       "      <th>Calc_year</th>\n",
       "      <th>FAIR_VALUE</th>\n",
       "      <th>ADDED_ON</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>33</th>\n",
       "      <td>AAPL</td>\n",
       "      <td>Technology</td>\n",
       "      <td>1.139593</td>\n",
       "      <td>Yes</td>\n",
       "      <td>2019</td>\n",
       "      <td>117.0</td>\n",
       "      <td>2019-12-18</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>206</th>\n",
       "      <td>ADI</td>\n",
       "      <td>Technology</td>\n",
       "      <td>1.249755</td>\n",
       "      <td>Yes</td>\n",
       "      <td>2019</td>\n",
       "      <td>72.0</td>\n",
       "      <td>2019-12-18</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>561</th>\n",
       "      <td>ALXN</td>\n",
       "      <td>Healthcare</td>\n",
       "      <td>1.558541</td>\n",
       "      <td>Yes</td>\n",
       "      <td>2019</td>\n",
       "      <td>26.0</td>\n",
       "      <td>2019-12-18</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1587</th>\n",
       "      <td>BIIB</td>\n",
       "      <td>Healthcare</td>\n",
       "      <td>0.987783</td>\n",
       "      <td>Yes</td>\n",
       "      <td>2019</td>\n",
       "      <td>160.0</td>\n",
       "      <td>2019-12-18</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1634</th>\n",
       "      <td>BKNG</td>\n",
       "      <td>Consumer Cyclical</td>\n",
       "      <td>1.038400</td>\n",
       "      <td>Yes</td>\n",
       "      <td>2019</td>\n",
       "      <td>842.0</td>\n",
       "      <td>2019-12-18</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     STOCK_TIKR             SECTOR      BETA GrowthEPS  Calc_year  FAIR_VALUE  \\\n",
       "33         AAPL         Technology  1.139593       Yes       2019       117.0   \n",
       "206         ADI         Technology  1.249755       Yes       2019        72.0   \n",
       "561        ALXN         Healthcare  1.558541       Yes       2019        26.0   \n",
       "1587       BIIB         Healthcare  0.987783       Yes       2019       160.0   \n",
       "1634       BKNG  Consumer Cyclical  1.038400       Yes       2019       842.0   \n",
       "\n",
       "        ADDED_ON  \n",
       "33    2019-12-18  \n",
       "206   2019-12-18  \n",
       "561   2019-12-18  \n",
       "1587  2019-12-18  \n",
       "1634  2019-12-18  "
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# growth investment logic (similar to value investment)\n",
    "\n",
    "from datetime import datetime\n",
    "from datetime import date\n",
    "import datetime\n",
    "import os\n",
    "from pandas import ExcelWriter\n",
    "\n",
    "\n",
    "dfcurrG = dfmean[dfmean.CURRENT_RATIO >= 1.5]\n",
    "dfdeG = dfmean[dfmean.DEBT_TO_EQUITY <= 0.4]\n",
    "dfdaG = dfmean[dfmean.DEBT_TO_ASSETS <= 1.1]\n",
    "dfroeG = dfmean[dfmean.ROE > 0.15]\n",
    "dfroicG = dfmean[dfmean.ROIC > 0.06]\n",
    "dfmktG = dfmean[dfmean.MARKET_CAP > 100000000]\n",
    "#dfepsGr = dfmean[dfmean.EPS_DILUTED_GROWTH > 0]\n",
    "\n",
    "#dfselect_stock.columns\n",
    "\n",
    "dfselect_stock.replace('',np.nan, inplace=True)\n",
    "dfselect_stock.fillna(0, inplace=True)\n",
    "rest_indx = dfselect_stock.reset_index()\n",
    "\n",
    "dfGrowth1 = pd.concat([dfcurrG,dfdeG,dfdaG,dfroeG,dfroicG,dfmktG])#dfepsGr)\n",
    "dfGrowth2 = pd.merge(dfGrowth1,dfEPS_GR, on='STOCK_TIKR')\n",
    "\n",
    "dfGrowth = pd.merge(dfGrowth2,df_fcf, on = 'STOCK_TIKR')\n",
    "# Select growth stock on basis of FCF\n",
    "# dfGrowth ['UNDERVALUED'] = np.where(dfGrowth.CLOSE_PRICE <= dfGrowth.TANGIBLE_ASSET_VALUE ,'Yes','No')\n",
    "#dfGrowth.head()\n",
    "\n",
    "def final_gr(dfGrowth):\n",
    "    return dfGrowth[\n",
    "        (dfGrowth.DEBT_TO_EQUITY <= 0.4)&\n",
    "        (dfGrowth.DEBT_TO_ASSETS <= 1.1)&\n",
    "        (dfGrowth.ROE >= 0.15)&\n",
    "        (dfGrowth.ROIC > 0.06)&\n",
    "        (dfGrowth.CURRENT_RATIO >= 1.5)&\n",
    "        (dfGrowth.GrowthEPS == 'Yes')&\n",
    "        (dfGrowth.MARKET_CAP > 10000000000)\n",
    "    ]\n",
    "        \n",
    "dfGrFinal = final_gr(dfGrowth)\n",
    "dfGrFinal = dfGrFinal[['STOCK_TIKR','SECTOR','BETA','GrowthEPS','Calc_year','FAIR_VALUE']].copy()\n",
    "dfGrFinal = dfGrFinal.drop_duplicates(subset=['STOCK_TIKR','SECTOR'],keep=\"first\")\n",
    "dfGrFinal['ADDED_ON'] = date.today()\n",
    "dfGrFinal.to_csv('Growth.csv')\n",
    "print('Below is the list of potential growth stocks:')\n",
    "dfGrFinal.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "There are 43 stocks which are potential growth stocks.\n"
     ]
    }
   ],
   "source": [
    "#dfGrowth.shape\n",
    "print('There are',dfGrFinal.shape[0],'stocks which are potential growth stocks.')\n",
    "dfGrFinal.to_csv('mygrowth.csv')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Once potential growth and value stocks have selected, we will check how many stocks are undervalued, so we can mark them with Buy flag. For that:\n",
    "- We will download the latest closing price of each potential value and growth stock.\n",
    "- Compare with our Fair value to select the undervalued stocks."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>symbol</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>AAPL</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>ADI</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>ALXN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>BIIB</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>BKNG</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  symbol\n",
       "0   AAPL\n",
       "1    ADI\n",
       "2   ALXN\n",
       "3   BIIB\n",
       "4   BKNG"
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>symbol</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>ACCO</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>ADM</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>AIR</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>ALG</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>AP</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  symbol\n",
       "0   ACCO\n",
       "1    ADM\n",
       "2    AIR\n",
       "3    ALG\n",
       "4     AP"
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# separating stock (value+Growth) symbol list (to check for the latest close price of porfolio stocks)\n",
    "\n",
    "dflookup = pd.DataFrame()\n",
    "dflookup['symbol'] = dfValFinal['STOCK_TIKR'].values  \n",
    "#dflookup.head()\n",
    "\n",
    "dflookupG = pd.DataFrame()\n",
    "dflookupG['symbol'] = dfGrFinal['STOCK_TIKR'].values\n",
    "dflookupG.head()\n",
    "\n",
    "dflookup_price =[dflookup,dflookupG]\n",
    "dflookup_price = pd.concat(dflookup_price)\n",
    "dflookup_price.head()\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(13601, 24)"
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    },
    {
     "data": {
      "text/plain": [
       "(46, 6)"
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    },
    {
     "data": {
      "text/plain": [
       "(46, 1)"
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    },
    {
     "data": {
      "text/plain": [
       "(18290, 27)"
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    },
    {
     "data": {
      "text/plain": [
       "(43, 7)"
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    },
    {
     "data": {
      "text/plain": [
       "(43, 1)"
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    },
    {
     "data": {
      "text/plain": [
       "(89, 1)"
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# crosscheck whether we are getting correct count in lookup as original portfolio list\n",
    "\n",
    "dfValue.shape\n",
    "dfValFinal.shape\n",
    "dflookup.shape\n",
    "\n",
    "dfGrowth.shape\n",
    "dfGrFinal.shape\n",
    "dflookupG.shape\n",
    "dflookup_price.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "C:\\Users\\Sudip\\Anaconda3\\lib\\site-packages\\pandas\\core\\frame.py:6211: FutureWarning: Sorting because non-concatenation axis is not aligned. A future version\n",
      "of pandas will change to not sort by default.\n",
      "\n",
      "To accept the future behavior, pass 'sort=False'.\n",
      "\n",
      "To retain the current behavior and silence the warning, pass 'sort=True'.\n",
      "\n",
      "  sort=sort)\n"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Below is the latest closing price for all the potential value and growth stocks.(showing first 5 results)\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>CLOSE_PRICE</th>\n",
       "      <th>STOCK_TIKR</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>9.585</td>\n",
       "      <td>ACCO</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>46.235</td>\n",
       "      <td>ADM</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>45.415</td>\n",
       "      <td>AIR</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>117.990</td>\n",
       "      <td>ALG</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2.990</td>\n",
       "      <td>AP</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   CLOSE_PRICE STOCK_TIKR\n",
       "0        9.585       ACCO\n",
       "1       46.235        ADM\n",
       "2       45.415        AIR\n",
       "3      117.990        ALG\n",
       "4        2.990         AP"
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Code to load latest closing price for the potential stocks\n",
    "\n",
    "import urllib.request, json\n",
    "import pandas as pd\n",
    "from pandas.io.json import json_normalize\n",
    "\n",
    "dfdataprice = pd.DataFrame()\n",
    "#dfdataprice.columns = ['STOCK_TIKR','CLOSE_PRICE']      \n",
    "\n",
    "rowcount = 0\n",
    "companies = dflookup_price['symbol']\n",
    "nodatalist =[]\n",
    "url = 'https://financialmodelingprep.com/api/v3/stock/real-time-price/code'\n",
    "cnt =0\n",
    "for x in companies:\n",
    "    #print(x)\n",
    "    newurl = url.replace('code',x)\n",
    "    #today = date.today().strftime(\"%Y-%m-%d\")\n",
    "    #print(today)\n",
    "    \n",
    "    #print(\"newur2\", newurl2)\n",
    "    mydata=[]\n",
    "    with urllib.request.urlopen(newurl) as url_pri:\n",
    "        #print(\"url_pri\",url_pri)\n",
    "        data1 = json.loads(url_pri.read().decode())\n",
    "        data = pd.DataFrame([data1])\n",
    "        #print(data)\n",
    "        if (data.empty != True):\n",
    "            mydata = data.copy()\n",
    "            #print(mydata)\n",
    "            if (dfdataprice.shape[0] ==0):\n",
    "                dfdataprice['symbol'] = \"\"\n",
    "            rowcount = dfdataprice.shape[0]\n",
    "            #print(rowcount)\n",
    "            dfdataprice = dfdataprice.append(mydata,ignore_index=True)\n",
    "            currrow = rowcount+len(mydata)\n",
    "            #print(currrow)\n",
    "            dfdataprice.iloc[rowcount:currrow,dfdataprice.columns.get_loc('symbol')]=x\n",
    "            #print(dfdataprice)\n",
    "        else:\n",
    "            nodatalist.append(x)\n",
    "\n",
    "print('Below is the latest closing price for all the potential value and growth stocks.(showing first 5 results)')\n",
    "dfdataprice.columns = ['CLOSE_PRICE','STOCK_TIKR']      \n",
    "#print('Companies that do not have data',nodatalist)\n",
    "#dfdataprice = dfdataprice.loc[:, dfdataprice.columns != 'index']\n",
    "dfdataprice.head()\n",
    "#saveToSQL('historical_price',dfdataprice,'replace')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>CLOSE_PRICE</th>\n",
       "      <th>STOCK_TIKR</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>9.585</td>\n",
       "      <td>ACCO</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>46.235</td>\n",
       "      <td>ADM</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>45.415</td>\n",
       "      <td>AIR</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>117.990</td>\n",
       "      <td>ALG</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2.990</td>\n",
       "      <td>AP</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   CLOSE_PRICE STOCK_TIKR\n",
       "0        9.585       ACCO\n",
       "1       46.235        ADM\n",
       "2       45.415        AIR\n",
       "3      117.990        ALG\n",
       "4        2.990         AP"
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "dfdataprice.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [],
   "source": [
    "def saveToSQL(tablename,dataframe,todo):\n",
    "    from sqlalchemy import create_engine\n",
    "    engine = create_engine('mysql+pymysql://nativeuser:password@localhost/automatic_portfolio_creation')\n",
    "    dataframe.to_sql(tablename, con = engine, if_exists=todo, chunksize = 500)\n",
    "    print('Data has been loaded to',tablename,'table')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 54,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Below is the list of undervalued Value stocks marked with the Buy flag as Yes or No:\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>STOCK_TIKR</th>\n",
       "      <th>SECTOR</th>\n",
       "      <th>BETA</th>\n",
       "      <th>FAIR_VALUE</th>\n",
       "      <th>Calc_year</th>\n",
       "      <th>ADDED_ON</th>\n",
       "      <th>CLOSE_PRICE</th>\n",
       "      <th>BUY_NOW</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>ACCO</td>\n",
       "      <td>Industrials</td>\n",
       "      <td>1.818291</td>\n",
       "      <td>19.0</td>\n",
       "      <td>2019</td>\n",
       "      <td>2019-12-18</td>\n",
       "      <td>9.585</td>\n",
       "      <td>Yes</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>ADM</td>\n",
       "      <td>Consumer Defensive</td>\n",
       "      <td>0.955928</td>\n",
       "      <td>45.0</td>\n",
       "      <td>2019</td>\n",
       "      <td>2019-12-18</td>\n",
       "      <td>46.235</td>\n",
       "      <td>Yes</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>AIR</td>\n",
       "      <td>Industrials</td>\n",
       "      <td>1.009082</td>\n",
       "      <td>28.0</td>\n",
       "      <td>2019</td>\n",
       "      <td>2019-12-18</td>\n",
       "      <td>45.415</td>\n",
       "      <td>No</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>ALG</td>\n",
       "      <td>Industrials</td>\n",
       "      <td>0.850361</td>\n",
       "      <td>39.0</td>\n",
       "      <td>2019</td>\n",
       "      <td>2019-12-18</td>\n",
       "      <td>117.990</td>\n",
       "      <td>No</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>AP</td>\n",
       "      <td>Industrials</td>\n",
       "      <td>2.154915</td>\n",
       "      <td>13.0</td>\n",
       "      <td>2019</td>\n",
       "      <td>2019-12-18</td>\n",
       "      <td>2.990</td>\n",
       "      <td>Yes</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  STOCK_TIKR              SECTOR      BETA  FAIR_VALUE  Calc_year    ADDED_ON  \\\n",
       "0       ACCO         Industrials  1.818291        19.0       2019  2019-12-18   \n",
       "1        ADM  Consumer Defensive  0.955928        45.0       2019  2019-12-18   \n",
       "2        AIR         Industrials  1.009082        28.0       2019  2019-12-18   \n",
       "3        ALG         Industrials  0.850361        39.0       2019  2019-12-18   \n",
       "4         AP         Industrials  2.154915        13.0       2019  2019-12-18   \n",
       "\n",
       "   CLOSE_PRICE BUY_NOW  \n",
       "0        9.585     Yes  \n",
       "1       46.235     Yes  \n",
       "2       45.415      No  \n",
       "3      117.990      No  \n",
       "4        2.990     Yes  "
      ]
     },
     "execution_count": 54,
     "metadata": {},
     "output_type": "execute_result"
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Data has been loaded to value_portfolio table\n"
     ]
    }
   ],
   "source": [
    "# Seecting stocks to buy which are undervalued and finalizing value stocks portfolio\n",
    "\n",
    "dfValPort = pd.merge(dfValFinal,dfdataprice,on='STOCK_TIKR', how='left')\n",
    "dfValPort['BUY_NOW'] = np.where(dfValPort['FAIR_VALUE']+ dfValPort['FAIR_VALUE']*(10/100) > dfValPort['CLOSE_PRICE'], 'Yes', 'No')\n",
    "dfValPort['SELL'] = 'No'\n",
    "dfValPort = dfValPort.loc[:, dfValPort.columns != 'SELL']\n",
    "print('Below is the list of undervalued Value stocks marked with the Buy flag as Yes or No:')\n",
    "dfValPort.head()\n",
    "\n",
    "saveToSQL('value_portfolio',dfValPort,'append') "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 55,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Below is the list of undervalued Growth stocks marked with the Buy flag as Yes or No:\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>STOCK_TIKR</th>\n",
       "      <th>SECTOR</th>\n",
       "      <th>BETA</th>\n",
       "      <th>GrowthEPS</th>\n",
       "      <th>Calc_year</th>\n",
       "      <th>FAIR_VALUE</th>\n",
       "      <th>ADDED_ON</th>\n",
       "      <th>CLOSE_PRICE</th>\n",
       "      <th>BUY_NOW</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>38</th>\n",
       "      <td>TEL</td>\n",
       "      <td>Technology</td>\n",
       "      <td>1.047484</td>\n",
       "      <td>Yes</td>\n",
       "      <td>2019</td>\n",
       "      <td>71.0</td>\n",
       "      <td>2019-12-18</td>\n",
       "      <td>95.450</td>\n",
       "      <td>No</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>39</th>\n",
       "      <td>TRIP</td>\n",
       "      <td>Consumer Cyclical</td>\n",
       "      <td>1.299399</td>\n",
       "      <td>Yes</td>\n",
       "      <td>2019</td>\n",
       "      <td>12.0</td>\n",
       "      <td>2019-12-18</td>\n",
       "      <td>29.195</td>\n",
       "      <td>No</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>40</th>\n",
       "      <td>UAA</td>\n",
       "      <td>Consumer Cyclical</td>\n",
       "      <td>0.419624</td>\n",
       "      <td>Yes</td>\n",
       "      <td>2019</td>\n",
       "      <td>3.0</td>\n",
       "      <td>2019-12-18</td>\n",
       "      <td>20.575</td>\n",
       "      <td>No</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>41</th>\n",
       "      <td>VLO</td>\n",
       "      <td>Energy</td>\n",
       "      <td>1.184196</td>\n",
       "      <td>Yes</td>\n",
       "      <td>2019</td>\n",
       "      <td>123.0</td>\n",
       "      <td>2019-12-18</td>\n",
       "      <td>95.670</td>\n",
       "      <td>Yes</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>42</th>\n",
       "      <td>WBA</td>\n",
       "      <td>Healthcare</td>\n",
       "      <td>0.807362</td>\n",
       "      <td>Yes</td>\n",
       "      <td>2019</td>\n",
       "      <td>74.0</td>\n",
       "      <td>2019-12-18</td>\n",
       "      <td>57.135</td>\n",
       "      <td>Yes</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   STOCK_TIKR             SECTOR      BETA GrowthEPS  Calc_year  FAIR_VALUE  \\\n",
       "38        TEL         Technology  1.047484       Yes       2019        71.0   \n",
       "39       TRIP  Consumer Cyclical  1.299399       Yes       2019        12.0   \n",
       "40        UAA  Consumer Cyclical  0.419624       Yes       2019         3.0   \n",
       "41        VLO             Energy  1.184196       Yes       2019       123.0   \n",
       "42        WBA         Healthcare  0.807362       Yes       2019        74.0   \n",
       "\n",
       "      ADDED_ON  CLOSE_PRICE BUY_NOW  \n",
       "38  2019-12-18       95.450      No  \n",
       "39  2019-12-18       29.195      No  \n",
       "40  2019-12-18       20.575      No  \n",
       "41  2019-12-18       95.670     Yes  \n",
       "42  2019-12-18       57.135     Yes  "
      ]
     },
     "execution_count": 55,
     "metadata": {},
     "output_type": "execute_result"
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Data has been loaded to growth_portfolio table\n"
     ]
    }
   ],
   "source": [
    "# Finalizing Growth-stocks portfolio\n",
    "\n",
    "dfGrPort = pd.merge(dfGrFinal,dfdataprice[['STOCK_TIKR','CLOSE_PRICE']],on='STOCK_TIKR', how='left')\n",
    "dfGrPort['BUY_NOW'] = np.where(dfGrPort['FAIR_VALUE'] + dfGrPort['FAIR_VALUE']*(10/100) > dfGrPort['CLOSE_PRICE'], 'Yes', 'No')\n",
    "print('Below is the list of undervalued Growth stocks marked with the Buy flag as Yes or No:')\n",
    "dfGrPort.tail()\n",
    "\n",
    "saveToSQL('growth_portfolio',dfGrPort,'append')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 56,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Below is total undervalued Value stocks out of potential stocks:\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "Yes    31\n",
       "No     15\n",
       "Name: BUY_NOW, dtype: int64"
      ]
     },
     "execution_count": 56,
     "metadata": {},
     "output_type": "execute_result"
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "\n",
      "\n",
      "Below is total undervalued Growth stocks out of potential stocks:\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "No     27\n",
       "Yes    16\n",
       "Name: BUY_NOW, dtype: int64"
      ]
     },
     "execution_count": 56,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Check the count of undervalued stocks\n",
    "print('Below is total undervalued Value stocks out of potential stocks:')\n",
    "dfValPort['BUY_NOW'].value_counts()\n",
    "print('\\n')\n",
    "print('Below is total undervalued Growth stocks out of potential stocks:')\n",
    "dfGrPort['BUY_NOW'].value_counts()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Below is the logic for rebalancing the portfolio:\n",
    "- We will update annual results each year (by analyzing data of previous five years from current annual year).\n",
    "- We will check if we find new stocks and add or append it to portfolio.\n",
    "- Both above steps will get executed with previous code\n",
    "\n",
    "- After 3 years we will check whether existing stocks are getting added for consistent three years.\n",
    "- If No, we will remove them from the portfolio buy marking them with the Sell flag.\n",
    "\n",
    "*Currently, SELL value is comins as Yes for all the stocks, as we are running code/appending stocks for testing purpose, in existing/current year(2019), once below code runs only once every 3 year, it will show correct results*"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 57,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "C:\\Users\\Sudip\\Anaconda3\\lib\\site-packages\\IPython\\core\\interactiveshell.py:2961: FutureWarning: 'STOCK_TIKR' is both an index level and a column label.\n",
      "Defaulting to column, but this will raise an ambiguity error in a future version\n",
      "  exec(code_obj, self.user_global_ns, self.user_ns)\n",
      "C:\\Users\\Sudip\\Anaconda3\\lib\\site-packages\\ipykernel_launcher.py:14: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame.\n",
      "Try using .loc[row_indexer,col_indexer] = value instead\n",
      "\n",
      "See the caveats in the documentation: http://pandas.pydata.org/pandas-docs/stable/indexing.html#indexing-view-versus-copy\n",
      "  \n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>STOCK_TIKR</th>\n",
       "      <th>SECTOR</th>\n",
       "      <th>BETA</th>\n",
       "      <th>FAIR_VALUE</th>\n",
       "      <th>ADDED_ON</th>\n",
       "      <th>CLOSE_PRICE</th>\n",
       "      <th>COUNT</th>\n",
       "      <th>SELL</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>ACCO</td>\n",
       "      <td>Industrials</td>\n",
       "      <td>1.818291</td>\n",
       "      <td>19.0</td>\n",
       "      <td>2019-12-18</td>\n",
       "      <td>9.585</td>\n",
       "      <td>2</td>\n",
       "      <td>Yes</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>ACCO</td>\n",
       "      <td>Industrials</td>\n",
       "      <td>1.818291</td>\n",
       "      <td>19.0</td>\n",
       "      <td>2019-12-18</td>\n",
       "      <td>9.585</td>\n",
       "      <td>2</td>\n",
       "      <td>Yes</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>ADM</td>\n",
       "      <td>Consumer Defensive</td>\n",
       "      <td>0.955928</td>\n",
       "      <td>45.0</td>\n",
       "      <td>2019-12-18</td>\n",
       "      <td>46.235</td>\n",
       "      <td>2</td>\n",
       "      <td>Yes</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>ADM</td>\n",
       "      <td>Consumer Defensive</td>\n",
       "      <td>0.955928</td>\n",
       "      <td>45.0</td>\n",
       "      <td>2019-12-18</td>\n",
       "      <td>46.235</td>\n",
       "      <td>2</td>\n",
       "      <td>Yes</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>AIR</td>\n",
       "      <td>Industrials</td>\n",
       "      <td>1.009082</td>\n",
       "      <td>28.0</td>\n",
       "      <td>2019-12-18</td>\n",
       "      <td>45.415</td>\n",
       "      <td>2</td>\n",
       "      <td>Yes</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  STOCK_TIKR              SECTOR      BETA  FAIR_VALUE    ADDED_ON  \\\n",
       "0       ACCO         Industrials  1.818291        19.0  2019-12-18   \n",
       "1       ACCO         Industrials  1.818291        19.0  2019-12-18   \n",
       "2        ADM  Consumer Defensive  0.955928        45.0  2019-12-18   \n",
       "3        ADM  Consumer Defensive  0.955928        45.0  2019-12-18   \n",
       "4        AIR         Industrials  1.009082        28.0  2019-12-18   \n",
       "\n",
       "   CLOSE_PRICE  COUNT SELL  \n",
       "0        9.585      2  Yes  \n",
       "1        9.585      2  Yes  \n",
       "2       46.235      2  Yes  \n",
       "3       46.235      2  Yes  \n",
       "4       45.415      2  Yes  "
      ]
     },
     "execution_count": 57,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Rebalance the value portfolio - To run once a 3 years\n",
    "\n",
    "engine = create_engine('mysql+pymysql://nativeuser:password@localhost/automatic_portfolio_creation')\n",
    "queryval = \"SELECT * from VALUE_PORTFOLIO\"\n",
    "\n",
    "dfportval =pd.read_sql(queryval,engine)\n",
    "mycountval = dfportval.groupby('STOCK_TIKR')['STOCK_TIKR'].count()\n",
    "dfcountval = pd.DataFrame(mycountval)\n",
    "dfcountval.rename(columns={'STOCK_TIKR':'COUNT'}, inplace=True)\n",
    "dfcountval['STOCK_TIKR'] = dfcountval.index\n",
    "dfReb = pd.merge(dfportval,dfcountval, on = 'STOCK_TIKR')\n",
    "dfRebVal = dfReb[['STOCK_TIKR','SECTOR','BETA','FAIR_VALUE','ADDED_ON','CLOSE_PRICE','COUNT']]\n",
    "#dfRebVal = dfReb.loc[:, dfReb.columns != 'BUY_NOW']\n",
    "dfRebVal ['SELL'] = np.where( dfRebVal['COUNT'] < 3,'Yes','No')\n",
    "dfRebVal.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 58,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "C:\\Users\\Sudip\\Anaconda3\\lib\\site-packages\\IPython\\core\\interactiveshell.py:2961: FutureWarning: 'STOCK_TIKR' is both an index level and a column label.\n",
      "Defaulting to column, but this will raise an ambiguity error in a future version\n",
      "  exec(code_obj, self.user_global_ns, self.user_ns)\n",
      "C:\\Users\\Sudip\\Anaconda3\\lib\\site-packages\\ipykernel_launcher.py:14: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame.\n",
      "Try using .loc[row_indexer,col_indexer] = value instead\n",
      "\n",
      "See the caveats in the documentation: http://pandas.pydata.org/pandas-docs/stable/indexing.html#indexing-view-versus-copy\n",
      "  \n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>STOCK_TIKR</th>\n",
       "      <th>SECTOR</th>\n",
       "      <th>BETA</th>\n",
       "      <th>ADDED_ON</th>\n",
       "      <th>CLOSE_PRICE</th>\n",
       "      <th>COUNT</th>\n",
       "      <th>SELL</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>AAPL</td>\n",
       "      <td>Technology</td>\n",
       "      <td>1.139593</td>\n",
       "      <td>2019-12-18</td>\n",
       "      <td>280.45</td>\n",
       "      <td>2</td>\n",
       "      <td>Yes</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>AAPL</td>\n",
       "      <td>Technology</td>\n",
       "      <td>1.139593</td>\n",
       "      <td>2019-12-18</td>\n",
       "      <td>280.45</td>\n",
       "      <td>2</td>\n",
       "      <td>Yes</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>ADI</td>\n",
       "      <td>Technology</td>\n",
       "      <td>1.249755</td>\n",
       "      <td>2019-12-18</td>\n",
       "      <td>118.88</td>\n",
       "      <td>2</td>\n",
       "      <td>Yes</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>ADI</td>\n",
       "      <td>Technology</td>\n",
       "      <td>1.249755</td>\n",
       "      <td>2019-12-18</td>\n",
       "      <td>118.88</td>\n",
       "      <td>2</td>\n",
       "      <td>Yes</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>ALXN</td>\n",
       "      <td>Healthcare</td>\n",
       "      <td>1.558541</td>\n",
       "      <td>2019-12-18</td>\n",
       "      <td>109.72</td>\n",
       "      <td>2</td>\n",
       "      <td>Yes</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  STOCK_TIKR      SECTOR      BETA    ADDED_ON  CLOSE_PRICE  COUNT SELL\n",
       "0       AAPL  Technology  1.139593  2019-12-18       280.45      2  Yes\n",
       "1       AAPL  Technology  1.139593  2019-12-18       280.45      2  Yes\n",
       "2        ADI  Technology  1.249755  2019-12-18       118.88      2  Yes\n",
       "3        ADI  Technology  1.249755  2019-12-18       118.88      2  Yes\n",
       "4       ALXN  Healthcare  1.558541  2019-12-18       109.72      2  Yes"
      ]
     },
     "execution_count": 58,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Rebalance the Growth portfolio - To run once a 3 years\n",
    "\n",
    "engine = create_engine('mysql+pymysql://nativeuser:password@localhost/automatic_portfolio_creation')\n",
    "queryGr = \"SELECT * from GROWTH_PORTFOLIO\"\n",
    "\n",
    "dfportGr =pd.read_sql(queryGr,engine)\n",
    "mycountGr = dfportGr.groupby('STOCK_TIKR')['STOCK_TIKR'].count()\n",
    "dfcountGr = pd.DataFrame(mycountGr)\n",
    "dfcountGr.rename(columns={'STOCK_TIKR':'COUNT'}, inplace=True)\n",
    "dfcountGr['STOCK_TIKR'] = dfcountGr.index\n",
    "dfRebG = pd.merge(dfportGr,dfcountGr, on = 'STOCK_TIKR')\n",
    "dfRebGr = dfRebG[['STOCK_TIKR','SECTOR','BETA','ADDED_ON','CLOSE_PRICE','COUNT']]\n",
    "#dfRebGr = dfReb.loc[:, dfReb.columns != 'BUY_NOW']\n",
    "dfRebGr ['SELL'] = np.where( dfRebGr['COUNT'] < 3,'Yes','No')\n",
    "dfRebGr.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "File Imported Successfully\n"
     ]
    }
   ],
   "source": [
    "print('File Imported Successfully')"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.7.0"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}
